<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * 패키지 조회와 구매를 담당
 */
class Package_model extends CI_Model {

	function __construct() {
		parent::__construct();
	}

	/**
	 * 패키지 상품 코드 리스트
	 */
	function select_package_list() {
		log_message('debug',"#### Product_model::select_package_list()");

		$this->db->where_not_in('package_cd', array(PREM_GUEST, PREM_GENERAL));
		$this->db->order_by('package_cd'); 
		$query = $this->db->get('tb_package') or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * 패키지 상품
	 */
	function get_package($package_cd) {
		log_message('debug',"#### Product_model::get_package($package_cd)");

		$this->db->where('package_cd', $package_cd); 
		$query = $this->db->get('tb_package') or $this->logsql->log_sql('error');
		$result = $query->row_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}

	/**
	 * 쿠폰 리스트
	 */
	function select_package_coupon_list($package_cd) {
		log_message('debug',"#### Package_model::select_package_coupon_list($package_cd)");

		$sql  = PHP_EOL." SELECT ";
		$sql .= PHP_EOL."       PC.* ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = PC.package_cd) package_name ";
		$sql .= PHP_EOL."     , (SELECT service_group_name FROM tb_service_group WHERE service_group_id = PC.service_group_id) service_group_name ";
		$sql .= PHP_EOL." FROM  tb_package_coupon PC ";
		$sql .= PHP_EOL." WHERE package_cd = ? ";

		$query = $this->db->query($sql, array($package_cd)) or $this->logsql->log_sql('error');
		$result = $query->result_array();
		
		$this->logsql->log_sql();
		$this->logsql->log_result($result);

		return $result;
	}
	
	/**
	 * 모든 패키지와 그에 속한 쿠폰 리스트
	 */
	function select_package_with_coupon_list() {
		log_message('debug',"#### Package_model::select_package_with_coupon_list()");
		
		$result = $this->select_package_list();
		
		for ($i=0; $i < count($result); $i++) { 
			$result[$i]['coupon'] = $this->select_package_coupon_list($result[$i]['package_cd']);
		}
		
		return $result;
	}
	
	/**
	 * 패키지와 그에 속한 쿠폰 리스트
	 */
	function get_package_with_coupon($package_cd) {
		log_message('debug',"#### Package_model::get_package_with_coupon($package_cd)");
		
		$package = $this->get_package($package_cd);
		
		$package['coupon'] = $this->select_package_coupon_list($package['package_cd']);
		
		return $package;
	}
	
	/**
	 * 패키지 상품 가입처리
	 */
	function insert_package_member($user_id, $package_cd, $start_dt) {
		log_message('debug',"#### Product_model::insert_package_member($user_id, $package_cd, $start_dt)");
		
		$this->db->trans_start();
		
		$package = $this->get_package_with_coupon($package_cd);
		$package_term = $package['term_months'];

		// 회원의 패키지 상품 가입 처리
		$this->db->set('user_id'       , $user_id);
		$this->db->set('package_cd'    , $package_cd);
		$this->db->set('vip_coupon_qty', $package['vip_coupon_qty']);
		$this->db->set('start_dt'      , $start_dt);
		$this->db->set('end_dt'        , "DATE_FORMAT(DATE_ADD(STR_TO_DATE('$start_dt','%Y%m%d'), INTERVAL $package_term MONTH),'%Y%m%d')", FALSE);
		$this->db->set('create_dt'     , 'now()', FALSE);
		$this->db->insert('tb_member_package') or $this->logsql->log_sql('error');

		// idx 가져오기
		$query = $this->db->get_where('tb_member_package',array('user_id'=>$user_id,'start_dt'=>$start_dt)) or $this->logsql->log_sql('error');
		$member_package = $query->row_array();
		$idx = $member_package['idx'];
		
		$this->logsql->log_sql();
		$this->logsql->log_result($member_package);
		
		// 패키지에 해당되는 쿠폰 지급
		$coupon_list = array();
		for ($i=0; $i < count($package['coupon']); $i++) { 
			$coupon_list[$i] = array(
				'idx'              => $idx,
				'service_group_id' => $package['coupon'][$i]['service_group_id'],
				'qty'              => $package['coupon'][$i]['qty']
			);
		}
		$this->db->insert_batch('tb_member_package_coupon', $coupon_list) or $this->logsql->log_sql('error');
		
		$this->db->trans_complete();
			
		$this->logsql->log_sql();
	}

	function get_vip_given_history($receiver_id) {
		log_message('debug',"#### Product_model::get_vip_given_history($receiver_id)");

		$sql  = PHP_EOL." SELECT V.* ";
		$sql .= PHP_EOL."     , (SELECT package_name FROM tb_package WHERE package_cd = V.package_cd) package_name ";
		$sql .= PHP_EOL."     , (SELECT end_dt FROM tb_member_package WHERE idx = V.member_package_idx) end_dt ";
		$sql .= PHP_EOL." FROM tb_vip_given_history V ";
		$sql .= PHP_EOL." WHERE receiver_id =  ? ";
		$sql .= PHP_EOL." AND confirm_yn =  'N' ";
		$sql .= PHP_EOL." LIMIT 1 ";
		$query = $this->db->query($sql, array($receiver_id)) or $this->logsql->log_sql('error');
		$result = $query->row_array();

		$this->logsql->log_sql();
		$this->logsql->log_result($result);
		
		return $result;
	}
	
	function update_vip_history_confirm_yn($idx) {
		log_message('debug',"#### Product_model::update_vip_history_confirm_yn($idx)");
		
		$this->db->set('confirm_yn','Y');
		$this->db->where('idx',$idx);
		$this->db->update('tb_vip_given_history') or $this->logsql->log_sql('error');

		$this->logsql->log_sql();
	}
}

/* End of file package_model.php */
/* Location: ./application/models/package/package_model.php */